using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using Company.BLL.Utils;
using Company.BLL.WS.DanhMucChuan;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace Company.BLL.DuLieuChuan
{
    public class NhomSanPham : BaseClass
    {
        public static DataSet SelectAll()
        {
            string sql = "select * from t_HaiQuan_DanhMucSanPhamGiaCong";
            DbCommand dbcom = db.GetSqlStringCommand(sql);
            return db.ExecuteDataSet(dbcom);
        }
        public  void WSGetAll()
        {
            DanhMucChuanWS ws_DMC = new DanhMucChuanWS();
            DataTable table = ws_DMC.GetDanhSachSPGiaCong().Tables[0];
            InsertUpdate(table);
        }

        public  string getDuKien(string masp)
        {
            string sql = "select madukien from t_HaiQuan_DanhMucSanPhamGiaCong where MaSanPham=@MaSanPham";
            DbCommand dbcom = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbcom, "@MaSanPham", SqlDbType.VarChar);
            object o = db.ExecuteScalar(dbcom);
            if (o == null)
                return null;
            return o.ToString();
        }
        public static string getTenSanPham(string masp)
        {
            string sql = "select TenSanPham from t_HaiQuan_DanhMucSanPhamGiaCong where MaSanPham=@MaSanPham";
            DbCommand dbcom = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbcom, "@MaSanPham", SqlDbType.VarChar,masp);
            object o = db.ExecuteScalar(dbcom);
            if (o == null)
                return null;
            return o.ToString();
        }
        #region Insert / Update methods.
        public bool InsertUpdate(DataTable table)
        {
            return this.InsertUpdateTransaction(table);
        }

        //---------------------------------------------------------------------------------------------
        public void InsertUpdateTransaction(string masp,string tensp,string dukien,string dvt_id,SqlTransaction transaction)
        {
            string spName = "p_t_HaiQuan_DanhMucSanPhamGiaCong_InsertUpdate";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();

            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@MaSanPham", SqlDbType.VarChar,masp);
            db.AddInParameter(dbCommand, "@MaDuKien", SqlDbType.VarChar, dukien);
            db.AddInParameter(dbCommand, "@DVT_ID", SqlDbType.Char, dvt_id);
            db.AddInParameter(dbCommand, "@TenSanPham", SqlDbType.NVarChar, tensp);          
            
            db.ExecuteNonQuery(dbCommand, transaction);

           
        }
        public bool InsertUpdateTransaction(DataTable table)
        {
            bool ret = false;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    foreach (DataRow row in table.Rows)
                    {
                        InsertUpdateTransaction(row[1].ToString(), FontConverter.TCVN2Unicode(row[2].ToString()), row[0].ToString(), row[5].ToString(),transaction);
                    }
                    transaction.Commit();
                    ret = true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            return ret;           
        }
        #endregion
    }
}
